
if exists(select name from sysobjects where name = 'sp_lopGetByMaKhoa')
	drop proc sp_lopGetByMaKhoa
go
/*
	Created by: Phuoc NH
	Created on: 14/01/2014 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_lopGetByMaKhoa.sql
*/

create proc sp_lopGetByMaKhoa
@makhoa varchar(100),
@userid int
as
begin
	declare @NhomUserID int
	set @NhomUserID=(select NhomUserid from TraCuuDiem2.dbo.Users where Userid=@Userid)
	if(@NhomUserID=1)
		begin
			select a.* from lop a
		end
	else
		begin
			SELECT     dbo.Lop.*, dbo.Khoa.MaKhoa
FROM         dbo.Nganh INNER JOIN
                      dbo.ChuyenNganh ON dbo.Nganh.MaNganh = dbo.ChuyenNganh.MaNganh INNER JOIN
                      dbo.Lop ON dbo.ChuyenNganh.MaCN = dbo.Lop.MaCN INNER JOIN
                      dbo.Khoa ON dbo.Nganh.MaKhoa = dbo.Khoa.MaKhoa where Khoa.MaKhoa=@makhoa
		end
			
	
end

